csv-file “hospital_points” and the polygon of North-Rhine Westphalia. How many hospitals are located within the borders of NRW?
data folder and a shapefile of NRW. For NRW, you can either use Stefans OSM syntax or use the “german_states” shapefile and filter on AGS=="05".
The default of st_join will leave you with a ‘left-join’ and returns a data object with all hospitals and matching district information for those which are located within NRW. You can reset the option to perform an ‘inner-join’ and keep only the observation which lay within the predefined area (st_join(x , y, join = "", left = FALSE)).
# load hospitals
hospitals <- read.csv("../data/hospital_points.csv",
header = T, fill = T, sep = ",") %>%
sf::st_as_sf(., coords = c("X", "Y"),
crs = 3035)
# use the OSM function provided by Stefan
nrw <-
osmdata::getbb(
"Nordrhein-Westfalen",
format_out = "sf_polygon"
) %>%
.$multipolygon %>%
sf::st_transform(3035)
# or import shapefile nrw
nrw <- sf::st_read(dsn = "../data",
layer = "GER_STATES",
quiet = T) %>%
sf::st_transform(.,3035) %>%
filter( AGS == "05")
# spatial join
nrw_hospitals <-
hospitals %>%
sf::st_join(.,
# point layer nrw
nrw,
# chose intersect or within
join = sf::st_intersects,
# option false will
# keep only the hospital
# which could be joined
left = FALSE)
nrw_hospitals
## Simple feature collection with 343 features and 26 fields
## geometry type: POINT
## dimension: XY
## bbox: xmin: 4039814 ymin: 3052783 xmax: 4277224 ymax: 3246338
## projected CRS: ETRS89-extended / LAEA Europe
## First 10 features:
## beds ADE GF BSG RS AGS SDV_RS GEN BEZ IBZ BEM NBD
## 288 568 2 4 1 05 05 051110000000 Nordrhein-Westfalen Land 20 -- ja
## 289 558 2 4 1 05 05 051110000000 Nordrhein-Westfalen Land 20 -- ja
## 290 365 2 4 1 05 05 051110000000 Nordrhein-Westfalen Land 20 -- ja
## 291 475 2 4 1 05 05 051110000000 Nordrhein-Westfalen Land 20 -- ja
## 292 439 2 4 1 05 05 051110000000 Nordrhein-Westfalen Land 20 -- ja
## 293 104 2 4 1 05 05 051110000000 Nordrhein-Westfalen Land 20 -- ja
## 294 641 2 4 1 05 05 051110000000 Nordrhein-Westfalen Land 20 -- ja
## 295 200 2 4 1 05 05 051110000000 Nordrhein-Westfalen Land 20 -- ja
## 296 226 2 4 1 05 05 051110000000 Nordrhein-Westfalen Land 20 -- ja
## 297 1161 2 4 1 05 05 051110000000 Nordrhein-Westfalen Land 20 -- ja
## SN_L SN_R SN_K SN_V1 SN_V2 SN_G FK_S3 NUTS RS_0 AGS_0 WSK
## 288 05 0 00 00 00 000 0 DEA 050000000000 05000000 2009-11-01
## 289 05 0 00 00 00 000 0 DEA 050000000000 05000000 2009-11-01
## 290 05 0 00 00 00 000 0 DEA 050000000000 05000000 2009-11-01
## 291 05 0 00 00 00 000 0 DEA 050000000000 05000000 2009-11-01
## 292 05 0 00 00 00 000 0 DEA 050000000000 05000000 2009-11-01
## 293 05 0 00 00 00 000 0 DEA 050000000000 05000000 2009-11-01
## 294 05 0 00 00 00 000 0 DEA 050000000000 05000000 2009-11-01
## 295 05 0 00 00 00 000 0 DEA 050000000000 05000000 2009-11-01
## 296 05 0 00 00 00 000 0 DEA 050000000000 05000000 2009-11-01
## 297 05 0 00 00 00 000 0 DEA 050000000000 05000000 2009-11-01
## EWZ KFL DEBKG_ID geometry
## 288 17912134 34112.32 DEBKGDL20000E6GR POINT (4095599 3127379)
## 289 17912134 34112.32 DEBKGDL20000E6GR POINT (4094348 3137304)
## 290 17912134 34112.32 DEBKGDL20000E6GR POINT (4099466 3133197)
## 291 17912134 34112.32 DEBKGDL20000E6GR POINT (4100738 3130402)
## 292 17912134 34112.32 DEBKGDL20000E6GR POINT (4096526 3129923)
## 293 17912134 34112.32 DEBKGDL20000E6GR POINT (4094841 3132032)
## 294 17912134 34112.32 DEBKGDL20000E6GR POINT (4101452 3130494)
## 295 17912134 34112.32 DEBKGDL20000E6GR POINT (4092521 3129609)
## 296 17912134 34112.32 DEBKGDL20000E6GR POINT (4094721 3127315)
## 297 17912134 34112.32 DEBKGDL20000E6GR POINT (4096628 3125783)
Did the operationalization of health care provision convince you? Don’t you think it might be more important how many hospitals are close to the respondents? To test this, we want to calculate the number of hospitals per district in North-Rhine Westphalia. Use the syntax below to prep the hospital data.
Earn extra points by counting not only the number of hospitals but also the sum of hospital beds within a district.
nrw_districts <- sf::st_read(dsn = "../data",
layer = "GER_DISTRICTS",
# quiet is optional if you
# not want to print the metainformation
quiet = T) %>%
sf::st_transform(. , 3035) %>%
dplyr::rename(., district_id = id) %>%
# filter the districts of NRW
dplyr::filter( district_id >= 5000 & district_id < 6000 )
nrw_hospitals <-
nrw_hospitals %>%
# beds were character, now numeric
dplyr::mutate(beds = as.numeric(beds)) %>%
# replace NAs as zeros for simplification
replace(., is.na(.), 0)
You need a as_tibble() data frame to use the functions group_by() and summarise().
The function n() allows summarising the total count of hospitals. sum(beds) for summarizing the bed total per district.
district_hospital_join <-
nrw_hospitals %>%
# join the hospitals
# within districts
sf::st_join(., nrw_districts, join = sf::st_within) %>%
# use as tibble to perform
# group by & summarise
dplyr::as_tibble() %>%
dplyr::group_by(district_id) %>%
dplyr::summarise(hospital_ct = n(),
hospital_bed_ct = sum(beds)) %>%
# left join the new information
# to the original data frame
left_join(nrw_districts,.)
## `summarise()` ungrouping output (override with `.groups` argument)
## Joining, by = "district_id"
district_hospital_join
## Simple feature collection with 53 features and 3 fields
## geometry type: MULTIPOLYGON
## dimension: XY
## bbox: xmin: 4031313 ymin: 3029642 xmax: 4283898 ymax: 3269981
## projected CRS: ETRS89-extended / LAEA Europe
## First 10 features:
## district_id hospital_ct hospital_bed_ct geometry
## 1 5111 12 5080 MULTIPOLYGON (((4094868 314...
## 2 5112 8 4607 MULTIPOLYGON (((4092415 316...
## 3 5113 13 5619 MULTIPOLYGON (((4114154 316...
## 4 5114 4 2093 MULTIPOLYGON (((4080863 314...
## 5 5116 5 2164 MULTIPOLYGON (((4075096 313...
## 6 5117 2 911 MULTIPOLYGON (((4105756 315...
## 7 5119 3 1507 MULTIPOLYGON (((4103124 316...
## 8 5120 3 1020 MULTIPOLYGON (((4128163 312...
## 9 5122 3 1145 MULTIPOLYGON (((4117277 312...
## 10 5124 3 2124 MULTIPOLYGON (((4130282 313...